package com.zretc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.zretc.dao.HomestayMessageAdminDao;
import com.zretc.entity.HomeInfo;
import com.zretc.entity.PageInfo;
import com.zretc.entity.RoomInfo;
import com.zretc.util.DBUtil;
/**
 * 
 * @author YuanDali
 *
 */
public class HomestayMessageAdminDaoImpl implements HomestayMessageAdminDao{

	/**
	 * 根据传入的参数不同进行分页查询民宿信息
	 * @param condition
	 * @return 返回一个分页信息对象
	 */
	@Override
	public PageInfo<Map<String, String>> findHomestayByCondition(Map<String, String> condition) {
		List<Map<String, String>> data = new ArrayList<>();
		StringBuilder sql = new StringBuilder();
		ResultSet rSet =null;
		Map< String, String> map = null;
		//获取页码和页容量
		String pageNumStr = condition.get("pageNum");
		String pageSizeStr = condition.get("pageSize");
		Integer pageNum = Integer.parseInt(pageNumStr);
		Integer pageSize = Integer.parseInt(pageSizeStr);
		sql.append("select homeinfo.home_id,home_name,home_address,home_phone,home_introduce,home_picture");
		sql.append(" from homeinfo left join homepictureinfo on homeinfo.home_id=homepictureinfo.home_id");
		sql.append(" left join styleinfo");
		sql.append(" on homeinfo.style_id=styleinfo.style_id");
		//判断查询条件
		if (condition.get("homeName")=="" && condition.get("homeAddress")=="" && "选择风格".equals(condition.get("homeStyle"))==true) {
			//若查询条件全为空串时进行全部查询
			sql.append(" group by homeinfo.home_id");
			sql.append(" limit ?,?");
			rSet = DBUtil.doQuery(sql.toString(), (pageNum-1)*pageSize,pageSize);
		}if (condition.get("homeName")=="" && condition.get("homeAddress")=="" && "选择风格".equals(condition.get("homeStyle"))==false) {
			sql.append(" where style_type=?");
			sql.append(" group by homeinfo.home_name");
			sql.append(" limit ?,?");
			rSet = DBUtil.doQuery(sql.toString(), condition.get("homeStyle"),(pageNum-1)*pageSize,pageSize);
		}if (condition.get("homeName")=="" && condition.get("homeAddress")!="" && "选择风格".equals(condition.get("homeStyle"))==false) {
			sql.append(" where home_address like ? and style_type=?");
			sql.append(" group by homeinfo.home_id");
			sql.append(" limit ?,?");
			rSet = DBUtil.doQuery(sql.toString(), "%"+condition.get("homeAddress")+"%",condition.get("homeStyle"),(pageNum-1)*pageSize,pageSize);
		}if (condition.get("homeName")=="" && condition.get("homeAddress")!="" && "选择风格".equals(condition.get("homeStyle"))==true) {
			sql.append(" where home_address like ?");
			sql.append(" group by homeinfo.home_id");
			sql.append(" limit ?,?");
			rSet = DBUtil.doQuery(sql.toString(), "%"+condition.get("homeAddress")+"%",(pageNum-1)*pageSize,pageSize);
		}if (condition.get("homeName")!="") {
			sql.append(" where home_name like ?");
			sql.append(" group by homeinfo.home_id");
			sql.append(" limit ?,?");
			rSet = DBUtil.doQuery(sql.toString(), "%"+condition.get("homeName")+"%",(pageNum-1)*pageSize,pageSize);
		}
		try {
			while(rSet.next()) {
				//获取民宿id
				String homestayId = rSet.getString("home_id");
				//获取民宿名
				String homestayName = rSet.getString("home_name");
				//获取民宿地址
				String homestayAddress = rSet.getString("home_address");
				//获取民宿电话
				String homestayPhone = rSet.getString("home_phone");
				//获取民宿介绍内容
				String homestayIntroduce = rSet.getString("home_introduce");
				//获取民宿照片
				String homestayPicture = rSet.getString("home_picture");
				map = new HashMap<String, String>();
				//将取到的信息存入map中
				map.put("homestayName", homestayName);
				map.put("homestayAddress", homestayAddress);
				map.put("homestayPhone", homestayPhone);
				map.put("homestayIntroduce", homestayIntroduce);
				map.put("homestayPicture", homestayPicture);
				map.put("homestayId", homestayId);
				data.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		       //统计记录总数
				StringBuilder sqlSum = new StringBuilder();
				Integer total = 0;
				sqlSum.append(" select count(*)");
				sqlSum.append(" from (select homeinfo.home_id");
				sqlSum.append(" from homeinfo left join homepictureinfo on homeinfo.home_id=homepictureinfo.home_id");
				sqlSum.append(" left join styleinfo");
				sqlSum.append(" on homeinfo.style_id=styleinfo.style_id");
				//判断查询条件
				if (condition.get("homeName")=="" && condition.get("homeAddress")=="" && "选择风格".equals(condition.get("homeStyle"))==true) {
					//若查询条件全为空串时进行全部查询
					sqlSum.append(" group by homeinfo.home_id) temp");
					rSet = DBUtil.doQuery(sqlSum.toString());
				}if (condition.get("homeName")=="" && condition.get("homeAddress")=="" && "选择风格".equals(condition.get("homeStyle"))==false) {
					sqlSum.append(" where style_type=?");
					sqlSum.append(" group by homeinfo.home_id) temp");
					rSet = DBUtil.doQuery(sqlSum.toString(), condition.get("homeStyle"));
				}if (condition.get("homeName")=="" && condition.get("homeAddress")!="" && "选择风格".equals(condition.get("homeStyle"))==false) {
					sqlSum.append(" where home_address like ? and style_type=?");
					sqlSum.append(" group by homeinfo.home_id) temp");
					rSet = DBUtil.doQuery(sqlSum.toString(), "%"+condition.get("homeAddress")+"%",condition.get("homeStyle"));
				}if (condition.get("homeName")=="" && condition.get("homeAddress")!="" && "选择风格".equals(condition.get("homeStyle"))==true) {
					sqlSum.append(" where home_address like ?");
					sqlSum.append(" group by homeinfo.home_id) temp");
					rSet = DBUtil.doQuery(sqlSum.toString(), "%"+condition.get("homeAddress")+"%");
				}if (condition.get("homeName")!="") {
					sqlSum.append(" where home_name like ?");
					sqlSum.append(" group by homeinfo.home_id) temp");
					rSet = DBUtil.doQuery(sqlSum.toString(), "%"+condition.get("homeName")+"%");
				}
				try {
					while(rSet.next()) {
					 total = rSet.getInt(1);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				PageInfo<Map<String, String>> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
		return pageInfo;
	}

	@Override
	public List<Map<String, String>> getRoomMessageById(String roomId) {
		StringBuilder sql = new StringBuilder();
		List<Map<String, String>> roomMassageList = new ArrayList<Map<String,String>>();
		sql.append("select room_state,room_price,styleinfo.style_id,room_picture,style_type");
		sql.append(" from roominfo");
		sql.append(" left join homepictureinfo on homepictureinfo.room_id=roominfo.room_id");
		sql.append(" left join styleinfo on roominfo.style_id=styleinfo.style_id");
		sql.append(" where roominfo.room_id=?");
		ResultSet rSet = DBUtil.doQuery(sql.toString(), roomId);
		try {
			while(rSet.next()) {
				//获取房间状态
				String roomState = rSet.getString("room_state");
				//获取房间价格
				String roomPrice = rSet.getString("room_price");
				//获取风格id
				String styleId = rSet.getString("style_id");
				//获取房间风格
				String styleType = rSet.getString("style_type");
				//获取房间照片
				String roomPicture = rSet.getString("room_picture");
				//创建map对象
				Map<String, String> map = new HashMap<String, String>();
				//将取到的信息存入map中
				map.put("roomId", roomId);
				map.put("roomState", roomState);
				map.put("roomPrice", roomPrice);
				map.put("styleId", styleId);
				map.put("styleType", styleType);
				map.put("roomPicture", roomPicture);
				//将map存入list中
				roomMassageList.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return roomMassageList;
	}

	/**
	 * 通过房间编号删除房间信息
	 * @param roomId
	 * @return
	 */
	@Override
	public int deleteRomeById(String roomId) {
		StringBuilder sql = new StringBuilder();
		sql.append("delete from roominfo where room_id=?");
		int i = DBUtil.doUpdate(sql.toString(), roomId);
		return i;
	}

	/**
	 * 通过民宿编号删除民宿信息
	 * @param roomId
	 * @return
	 */
	@Override
	public int deleteHomeById(String homeId) {
		StringBuilder sql = new StringBuilder();
		sql.append("delete from homeinfo where home_id=?");
		int i = DBUtil.doUpdate(sql.toString(), homeId);
		return i;
	}

	@Override
	public int addHomestay(HomeInfo homeInfo) {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into homeinfo(home_id,home_name,home_address,home_phone,home_introduce,style_id)");
		sql.append(" value(?,?,?,?,?,?)");
		int i = DBUtil.doUpdate(sql.toString(),homeInfo.getHomeId(),homeInfo.getHomeName(),homeInfo.getHomeAddress(),homeInfo.getHomePhone(),homeInfo.getHomeIntroduce(),homeInfo.getHomeStyle());
		return i;
	}

	@Override
	public int addRoom(Map<String, Object> roomMessage) {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into roominfo(room_id,room_state,room_price,home_id,style_id)");
		sql.append(" value(?,?,?,?,?)");
		int i = DBUtil.doUpdate(sql.toString(),roomMessage.get("roomId"),roomMessage.get("roomState"),roomMessage.get("roomPrice"),roomMessage.get("homeId"),roomMessage.get("styleId"));
		return i;
	}

	/**
	 * 修改民宿信息
	 */
	@Override
	public int updateHomestay(HomeInfo homeInfo) {
		StringBuilder sql = new StringBuilder();
		sql.append("update homeinfo set");
		sql.append(" home_name=?,home_address=?,home_phone=?,home_introduce=?");
		sql.append(" where home_id=?");
		int i = DBUtil.doUpdate(sql.toString(),homeInfo.getHomeName(),homeInfo.getHomeAddress(),homeInfo.getHomePhone(),homeInfo.getHomeIntroduce(),homeInfo.getHomeId());
		return i;
	}

	/**
	 * 修改房间信息
	 */
	@Override
	public int updateRoom(Map<String, Object> roomMessage) {
		StringBuilder sql = new StringBuilder();
		sql.append("update roominfo set");
		sql.append(" room_state=?,room_price=?,style_id=?");
		sql.append(" where room_id=?");
		int i = DBUtil.doUpdate(sql.toString(),roomMessage.get("roomState"),roomMessage.get("roomPrice"),roomMessage.get("styleId"),roomMessage.get("roomId"));
		return i;
	}

	@Override
	public String getStyleIdByStyleName(String styleName) {
		StringBuilder sql = new StringBuilder();
		String styleId = null;
		sql.append("select style_id from styleinfo where style_type=?");
		ResultSet rSet = DBUtil.doQuery(sql.toString(), styleName);
		try {
			while(rSet.next()) {
				 styleId = rSet.getString("style_id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return styleId;
	}

}



























